# -*- coding:utf-8 -*-
import os.path

import pandas as pd

from infrastructure.utils.dateUtil import generatePastDateString
from infrastructure.utils.dbUtil import SqliteUtil, SqliteConfig
from config.baseConfiguration import Sqlite3Config, ReportConfig
from infrastructure.utils.result import R

config = SqliteConfig(dbPath=Sqlite3Config.dbPath)
filename_nodes = 'nodes.csv'
filename_edges = 'edges.csv'
filename_nodes = os.path.join(ReportConfig.reportPath, filename_nodes)
filename_edges = os.path.join(ReportConfig.reportPath, filename_edges)

def popularityGraphView() -> R:
    """
    人气股板块图谱展示
    :return:
    """
    pbSql = """
        select b.code
            , case when m.zf_close is not null then i.name || '-' || b.count || '-' || b.dti || '-' || round(m.zf_close, 2) 
                else i.name || '-' || b.count || '-' || b.dti end as label
            , 0 as type
            , max(b.count) c, b.dti, m.zf_close
        from stock_board b
            left join stock_move m on m.code=b.code and m.dti=b.tdti
            left join stock_info i on b.code = i.code
        where b.count > 2
        group by b.code
    """
    db = SqliteUtil.get_instance(dbConfig=config)
    pbList = db.get_all(pbSql)
    if pbList is None or len(pbList) == 0:
        return R.fail(msg='没有人气股数据')
    pbCodeList = [r[0] for r in pbList]

    pbGroupSql = f"""
        select distinct l.code, l.group_id || ''
        from stock_board_logic l
            left join group_info i on l.group_id = i.id
        where l.type = 0 and l.code in ('{"','".join(pbCodeList)}') and l.group_id not in (614,618,619,625)
    """
    pbGroupList = db.get_all(pbGroupSql)

    if pbGroupList is None or len(pbGroupList) == 0:
        return R.fail(msg='没有人气股板块数据')

    gIdList = [r[1] for r in pbGroupList]

    grSql = f"""
        select r.group_id || ''
            , '[' || case when i.type==0 then '概' when i.type==1 then '行' else '区' end || ']' || i.name || '-' || r.ulbc || '-' || r.mculbc as label
            , 1 as type
            , r.ulbc
            , r.dti
            , r.mculbc
        from group_rotation r 
        left join group_info i on r.group_id = i.id
        where r.group_id in ({','.join(gIdList)})
    """
    grList = db.get_all(grSql)

    df_nodes = pd.DataFrame(pbList + grList, columns=['id', 'label', 'type', 'count', 'dti', 'variable'])
    df_nodes.to_csv(filename_nodes, index=False, header=True)

    df_edges = pd.DataFrame(pbGroupList, columns=['source', 'target'])
    df_edges.to_csv(filename_edges, index=False, header=True)


if __name__ == "__main__":
    popularityGraphView()
